package com.mycity.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.http.impl.client.DefaultBackoffStrategy;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mycity.bean.Gamer;
import com.mycity.bean.Post;
import com.mycity.bean.Tag;
import com.mycity.bean.User;
import com.mycity.gamification.LevelCalculator;
import com.mycity.mapper.PostRowMapper;
import com.mycity.mapper.TagRowMapper;
import com.mycity.mapper.UserRowMapper;
import com.mysql.jdbc.PreparedStatement;


public class PostDao extends RDSDao{

	GameDao gameDao = new GameDao();

	public List<Post> retrievePostsByUserId(int userId) {
		Object params[] = { userId };
		List<Post> posts = this.getTemplate().query("SELECT * from Post where userId=? ORDER BY postId DESC", params, new PostRowMapper());   	    

		return posts;
	}

	public List<Post> getPostsByLocation(int locationId) {
		Object params[] = { locationId };
		List<Post> posts = this.getTemplate().query("SELECT * from Post where locationId=? ORDER BY postId DESC", params, new PostRowMapper());   	    

		return posts;
	}

	public List<Post> getPostsByTag(String tag){
		String query = "SELECT Post.* FROM Post, TagPost, Tag WHERE Tag.tagName=? AND TagPost.tagId = Tag.tagId AND TagPost.postId = Post.postId ORDER BY postId DESC";
		Object params[] = { tag };
		List<Post> posts = this.getTemplate().query(query, params, new PostRowMapper());   	    
		return posts;
	}

	public Post getPost(int postId) {
		Object params[] = { postId };
		List<Post> posts = this.getTemplate().query("SELECT * from Post where postId=?", params, new PostRowMapper()); 

		return posts.get(0);
	}

	public String[] getTagsByPostId(int postId) {
		Object params[] = { postId };
		List<Tag> tagList = this.getTemplate().query("SELECT Tag.* from Tag, TagPost where Tag.tagId=TagPost.tagId and postId=?", params, new TagRowMapper() ); 
		String[] tagArray = new String[tagList.size()];
		for(int i=0; i<tagList.size(); i++){
			tagArray[i]=tagList.get(i).getTagName();

		}
		return tagArray;
	}

	public void setTagsByPostId(String[] tags, int postId){
		for(int i = 0; i<tags.length; i++){
			int tagId = setGetTag(tags[i]);
			setTagPost(tagId, postId);
		}

	}

	public int setGetTag(String tagName) {
		Object params[] = { tagName };
		try{
			this.getTemplate().update("INSERT INTO Tag(tagName) VALUES(?)", params); 
		} catch(Exception e){

		}

		List<Tag> tagList = this.getTemplate().query("SELECT * from Tag where tagName=?", params, new TagRowMapper() ); 

		return tagList.get(0).getTagId();
	}

	public void setTagPost(int tagId, int postId) {
		Object params[] = { tagId, postId };
		this.getTemplate().update("INSERT INTO TagPost(tagId, postId) VALUES(?,?)", params); 

	}



	public int writePost(final int userId, final int postType, final String postContent, final String postDate, final int locationId, final String locationName){
		final String insertSQL = "INSERT INTO Post(postType, postContent, postDate, userId, locationId, locationName) VALUES(?,?,?,?,?,?)";

		KeyHolder holder = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {           


			@Override
			public java.sql.PreparedStatement createPreparedStatement(
					Connection con) throws SQLException {
				PreparedStatement ps = (PreparedStatement) con.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, postType);
				ps.setString(2, postContent);
				ps.setString(3, postDate);
				ps.setInt(4, userId);
				ps.setInt(5, locationId);
				ps.setString(6, locationName);
				return ps;
			}
		}, holder);

		int postId = holder.getKey().intValue();

		Gamer gamer = gameDao.getGamer(userId);		
		gamer.setNumPosts(gamer.getNumPosts()+1);
		gamer.setExperience(LevelCalculator.updateExp(LevelCalculator.Objects.POST, gamer.getExperience(), 1));
		gameDao.updateGamer(gamer);

		return postId;
	}

	public List<Post> recentPosts(){
		List<Post> posts = this.getTemplate().query("SELECT * FROM Post ORDER BY postId DESC LIMIT 20",new PostRowMapper());
		return posts;
	}

	public void giveDiamond(int postId, int userId, String date){
		Object params[] = { postId, userId, date };
		this.getTemplate().update("INSERT INTO Diamond(postId,userId,date) VALUES(?,?,?)", params); 
		int diamondOwner = this.getTemplate().queryForInt("SELECT userId FROM Post WHERE postId="+postId);
		Gamer gamer = gameDao.getGamer(diamondOwner);		
		gamer.setNumDiamonds(gamer.getNumDiamonds()+1);
		gamer.setExperience(LevelCalculator.updateExp(LevelCalculator.Objects.DIAMOND, gamer.getExperience(), 1));
		gameDao.updateGamer(gamer);
	}

	public void removeDiamond(int postId, int userId){
		int diamondOwner = this.getTemplate().queryForInt("SELECT userId FROM Post WHERE postId="+postId);
		List<Post> diamondedPost = diamondedPost(userId, postId);

		if(diamondedPost.size()>0){
			Object params[] = { postId, userId };
			this.getTemplate().update("DELETE FROM Diamond WHERE postId=? AND userId=?", params);
			Gamer gamer = gameDao.getGamer(diamondOwner);		
			gamer.setNumDiamonds(gamer.getNumDiamonds()-1);
			gamer.setExperience(LevelCalculator.updateExp(LevelCalculator.Objects.DIAMOND, gamer.getExperience(), -1));
			gameDao.updateGamer(gamer);
		}
	}

	public List<Post> getPreviousDiamondedPosts(int userId){
		String query = "SELECT Post.* FROM Post, Diamond WHERE Post.postId=Diamond.postId AND Diamond.userId=?";
		Object params[] = { userId };
		List<Post> posts = this.getTemplate().query(query, params, new PostRowMapper());   	    
		return posts;
	}

	public List<Post> diamondedPost(int userId, int postId){
		String query = "SELECT Post.* FROM Diamond, Post WHERE Diamond.postId=? AND Diamond.userId=? AND Post.postId=Diamond.postId";
		Object params[] = { postId, userId };
		List<Post> posts = this.getTemplate().query(query, params, new PostRowMapper());   	    
		return posts;
	}

	public void deletePost(int postId){
		int userId = this.getTemplate().queryForInt("SELECT userId FROM Post WHERE postId="+postId);
		Gamer gamer = gameDao.getGamer(userId);		
		gamer.setNumPosts(gamer.getNumPosts()-1);
		gamer.setExperience(LevelCalculator.updateExp(LevelCalculator.Objects.POST, gamer.getExperience(), -1));
		gameDao.updateGamer(gamer);
		Object params[] = { postId };
		this.getTemplate().update("DELETE FROM Post WHERE postId=?", params);
	}

	public List<User> whoDiamonded(int postId){
		String query = "SELECT User.* FROM Diamond, User WHERE Diamond.postId=? AND Diamond.userId=User.userId";
		Object params[] = { postId };
		List<User> users = this.getTemplate().query(query, params, new UserRowMapper());   	    
		return users;
	}
}
